﻿using System;
using System.Data.SqlTypes;
using System.Globalization;
using System.Data;
using System.Data.SqlClient;
using ContractManagementSystem.Entity;

namespace ContractManagementSystem.DataAccess
{
    public class TimesheetDataAccess : SqlProvider
    {
        
        #region Private Fiel
        private SqlDataReader _sqlDataReader;
        #endregion

        #region Function
        /// <summary>
        /// Function Update Timesheet.
        /// Author: Group2.TuanCH - 10:46AM 07/05/2012
        /// </summary>
        /// <param name="timesheetEntity">ContractManagementSystem.Entity.TimesheetEntity</param>
        /// <returns> 1 if update succeed; otherwise 0 </returns>
        public int Update(TimesheetEntity timesheetEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //_logDataAccess.WriteError(ErrorMessage,);
                return 0;
            }

            int result = 0;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_Update", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", timesheetEntity.TimesheetId));
                    sqlCommand.Parameters.Add(new SqlParameter("@Description", timesheetEntity.Description));
                    sqlCommand.Parameters.Add(new SqlParameter("@WorkingTime", timesheetEntity.WorkingTime));
                    sqlCommand.Parameters.Add(new SqlParameter("@WorkDate", timesheetEntity.WorkDate));

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Insert Timesheet.
        /// Author: Group2.TuanCH - 11:06AM 07/05/2012
        /// </summary>
        /// <param name="timesheetEntity">ContractManagementSystem.Entity.TimesheetEntity</param>
        /// <returns> 1 if insert succeed; otherwise 0 </returns>
        public int Insert(TimesheetEntity timesheetEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return 0;
            }

            int result = 0;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_Insert", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@ContractId", timesheetEntity.ContractId));
                    sqlCommand.Parameters.Add(new SqlParameter("@WorkingTime", timesheetEntity.WorkingTime));
                    sqlCommand.Parameters.Add(new SqlParameter("@WorkDate", timesheetEntity.WorkDate));
                    sqlCommand.Parameters.Add(new SqlParameter("@Description", timesheetEntity.Description));

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch(SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Delete Timesheet.
        /// Author: Group2.TuanCH - 11:12AM 07/05/2012
        /// </summary>
        /// <param name="id">Guid: ContractManagementSystem.Entity.TimesheetEntity.Id</param>
        /// <returns> 1 if delete succeed; otherwise 0 </returns>
        public int Delete(Guid id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return 0;
            }

            int result = 0;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_Delete", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", id));

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Reject Timesheet.
        /// Author: Group2.TuanCH - 1:41PM 07/05/2012
        /// </summary>
        /// <param name="id">Guid: ContractManagementSystem.Entity.TimesheetEntity.Id</param>
        /// <param name="content">string: ContractManagementSystem.Entity.ReasonEntity.Content</param>
        /// <returns> 1 if reject succeed; otherwise 0 </returns>
        public int RejectTimesheet(Guid id, string content)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return 0;
            }

            int result = 0;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_RejectTimesheet", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", id));
                    sqlCommand.Parameters.Add(new SqlParameter("@Reason", content));

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Approved Timesheet.
        /// Author: Group2.TuanCH - 9:26AM 07/10/2012
        /// </summary>
        /// <param name="id">Guid: ContractManagementSystem.Entity.TimesheetEntity.Id</param>
        /// <returns> 1 if reject succeed; otherwise 0 </returns>
        public int ApprovedTimesheet(Guid id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return 0;
            }

            int result = 0;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_Approved", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", id));

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Inactive Timsheet.
        /// Author: Group2.TuanCH - 9:31AM 07/10/2012
        /// </summary>
        /// <param name="id">Guid: ContractManagementSystem.Entity.TimesheetEntity.Id</param>
        /// <returns> 1 if reject succeed; otherwise 0 </returns>
        public int Inactive(Guid id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return 0;
            }

            int result = 0;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_Inactive", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", id));

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function BatchUpdate
        /// Author: Group2.TuanCH - 09:40AM 07/18/2012
        /// </summary>
        /// <param name="source">DataTable</param>
        /// <param name="batchType">BatchType</param>
        /// <returns>Int: Number of records affected</returns>
        public int BatchUpdate(DataTable source, BatchType batchType)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return 0;
            }
            int result = 0;
            try
            {
                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.UpdatedRowSource = UpdateRowSource.None;
                    switch (batchType)
                    {
                        case BatchType.Update:
                            sqlCommand.CommandText = "sp_Timesheet_Update";
                            sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", SqlDbType.UniqueIdentifier, 16, source.Columns["TimesheetId"].ColumnName));
                            sqlCommand.Parameters.Add(new SqlParameter("@Description", SqlDbType.NText, 16000, source.Columns["Description"].ColumnName));
                            sqlCommand.Parameters.Add(new SqlParameter("@WorkingTime", SqlDbType.Float, 8, source.Columns["WorkingTime"].ColumnName));
                            sqlCommand.Parameters.Add(new SqlParameter("@WorkDate", SqlDbType.DateTime, 8, source.Columns["WorkDate"].ColumnName));
                            break;
                        case BatchType.Insert:
                            sqlCommand.CommandText = "sp_Timesheet_Insert";
                            sqlCommand.Parameters.Add(new SqlParameter("@ContractId", SqlDbType.UniqueIdentifier, 16, source.Columns["ContractId"].ColumnName));
                            sqlCommand.Parameters.Add(new SqlParameter("@Description", SqlDbType.NText, 16000, source.Columns["Description"].ColumnName));
                            sqlCommand.Parameters.Add(new SqlParameter("@WorkingTime", SqlDbType.Float, 8, source.Columns["WorkingTime"].ColumnName));
                            sqlCommand.Parameters.Add(new SqlParameter("@WorkDate", SqlDbType.DateTime, 8, source.Columns["WorkDate"].ColumnName));
                            break;
                        case BatchType.Delete:
                            sqlCommand.CommandText = "sp_Timesheet_Delete";
                            sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", SqlDbType.UniqueIdentifier, 16, source.Columns["TimesheetId"].ColumnName));
                            break;
                        case BatchType.Inactive:
                            sqlCommand.CommandText = "sp_Timesheet_Inactive";
                            sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", SqlDbType.UniqueIdentifier, 16, source.Columns["TimesheetId"].ColumnName));
                            break;
                        case BatchType.Reject:
                            sqlCommand.CommandText = "sp_Timesheet_RejectTimesheet";
                            sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", SqlDbType.UniqueIdentifier, 16, source.Columns["TimesheetId"].ColumnName));
                            sqlCommand.Parameters.Add(new SqlParameter("@Reason", SqlDbType.NText, 16, source.Columns["Reason"].ColumnName));
                            break;
                        case BatchType.Approved:
                            sqlCommand.CommandText = "sp_Timesheet_Approved";
                            sqlCommand.Parameters.Add("@TimesheetId", SqlDbType.UniqueIdentifier, 16, source.Columns["TimesheetId"].ColumnName);
                            break;
                    }
                    sqlCommand.Connection = CreateConnection();
                    var sqlDataAdapter = new SqlDataAdapter { InsertCommand = sqlCommand, UpdateBatchSize = source.Rows.Count };
                    result = sqlDataAdapter.Update(source);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }
        
        /// <summary>
        /// Function Get all Timesheet data
        /// Author: Group2.TuanCH - 9:46AM 07/10/2012
        /// </summary>
        /// <returns> DataTable of timesheet </returns>
        public DataTable FetchAll()
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("TimesheetId", typeof(string));
            dataTable.Columns.Add("WorkDate", typeof(DateTime));
            dataTable.Columns.Add("WorkingTime", typeof(float));
            dataTable.Columns.Add("Description", typeof(string));
            dataTable.Columns.Add("Status", typeof(string));
            dataTable.Columns.Add("Id", typeof(int));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }

            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_SelectAll", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    _sqlDataReader = sqlCommand.ExecuteReader();
                    if (_sqlDataReader.HasRows)
                    {
                        int id = 0;
                        while (_sqlDataReader.Read())
                        {
                            string timesheetId = Convert.ToString(new Guid(_sqlDataReader["TimesheetId"].ToString()), CultureInfo.CurrentCulture);
                            DateTime createDate = Convert.ToDateTime(_sqlDataReader["WorkDate"], CultureInfo.CurrentCulture);
                            float workingTime = float.Parse(_sqlDataReader["WorkingTime"].ToString(), CultureInfo.CurrentCulture);
                            string description = _sqlDataReader["Description"].ToString();
                            string status = _sqlDataReader["Name"].ToString();
                            if (id == 14) id = 0;
                            dataTable.Rows.Add(timesheetId, createDate, workingTime, description, status, id);
                            id++;
                        }
                    }

                    _sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// Function Get Timesheet data by ContractId
        /// Author: Group2.TuanCH - 10:07AM 07/10/2012
        /// </summary>
        /// <param name="contractId">Guid: ContractManagementSystem.Entity.ContractEntity.ContractId</param>
        /// <returns> DataTable of timesheet </returns>
        public DataTable FetchByContractId(Guid contractId)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("TimesheetId", typeof(Guid));
            dataTable.Columns.Add("WorkDate", typeof(DateTime));
            dataTable.Columns.Add("WorkingTime", typeof(float));
            dataTable.Columns.Add("Description", typeof(string));
            dataTable.Columns.Add("Status", typeof(string));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }

            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_SelectByContractId", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@ContractId", contractId));
                    _sqlDataReader = sqlCommand.ExecuteReader();
                    if (_sqlDataReader.HasRows)
                    {
                        while (_sqlDataReader.Read())
                        {
                            var timesheetId = new Guid(_sqlDataReader["TimesheetId"].ToString());
                            DateTime createDate = Convert.ToDateTime(_sqlDataReader["WorkDate"].ToString(), CultureInfo.CurrentCulture);
                            float workingTime = float.Parse(_sqlDataReader["WorkingTime"].ToString(), CultureInfo.CurrentCulture);
                            string description = _sqlDataReader["Description"].ToString();
                            string status = _sqlDataReader["Name"].ToString();

                            dataTable.Rows.Add(timesheetId, createDate, workingTime, description, status);
                        }
                    }

                    _sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// Function Get Timesheet data by TimesheetId
        /// Author: Group2.TuanCH - 08:52PM 07/15/2012
        /// </summary>
        /// <param name="timesheetId">Guid: ContractManagementSystem.Entity.ContractEntity.ContractId</param>
        /// <returns> DataTable of timesheet by TimesheetId </returns>
        public DataTable FetchByTimesheetId(Guid timesheetId)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("TimesheetId", typeof (Guid));
            dataTable.Columns.Add("WorkDate", typeof(DateTime));
            dataTable.Columns.Add("WorkingTime", typeof(float));
            dataTable.Columns.Add("Description", typeof(string));
            dataTable.Columns.Add("Status", typeof(string));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }

            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_SelectByTimesheetId", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", timesheetId));
                    _sqlDataReader = sqlCommand.ExecuteReader();
                    if (_sqlDataReader.HasRows)
                    {
                        while (_sqlDataReader.Read())
                        {
                            DateTime createDate = Convert.ToDateTime(_sqlDataReader["WorkDate"], CultureInfo.CurrentCulture);
                            float workingTime = float.Parse(_sqlDataReader["WorkingTime"].ToString(), CultureInfo.CurrentCulture);
                            string description = _sqlDataReader["Description"].ToString();
                            string status = _sqlDataReader["Name"].ToString();

                            dataTable.Rows.Add(timesheetId, createDate, workingTime, description, status);
                        }
                    }

                    _sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable FetchByUserId(Guid userId)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("TimesheetId", typeof(Guid));
            dataTable.Columns.Add("WorkDate", typeof(DateTime));
            dataTable.Columns.Add("WorkingTime", typeof(float));
            dataTable.Columns.Add("Description", typeof(string));
            dataTable.Columns.Add("Status", typeof(string));
            dataTable.Columns.Add("Id", typeof(int));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }

            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_SelectByUserId", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@UserId", userId));
                    _sqlDataReader = sqlCommand.ExecuteReader();
                    if (_sqlDataReader.HasRows)
                    {
                        int id = 0;
                        while (_sqlDataReader.Read())
                        {
                            var timesheetId = new Guid(_sqlDataReader["TimesheetId"].ToString());
                            DateTime createDate = Convert.ToDateTime(_sqlDataReader["WorkDate"], CultureInfo.CurrentCulture);
                            float workingTime = float.Parse(_sqlDataReader["WorkingTime"].ToString(), CultureInfo.CurrentCulture);
                            string description = _sqlDataReader["Description"].ToString();
                            string status = _sqlDataReader["Name"].ToString();
                            if (id == 14) id = 0;

                            dataTable.Rows.Add(timesheetId, createDate, workingTime, description, status,id);
                            id++;
                        }
                    }

                    _sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <param name="contractId"></param>
        /// <param name="contractorId"></param>
        /// <param name="statusParemeter"></param>
        /// <returns></returns>
        public DataTable TimesheetSearch(DateTime fromDate, DateTime toDate, Guid contractId, Guid contractorId, string statusParameter)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("TimesheetId", typeof(Guid));
            dataTable.Columns.Add("WorkDate", typeof(DateTime));
            dataTable.Columns.Add("WorkingTime", typeof(float));
            dataTable.Columns.Add("Description", typeof(string));
            dataTable.Columns.Add("Status", typeof(string));
            dataTable.Columns.Add("Id", typeof(int));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }

            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_Search", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@FromDate", fromDate));
                    sqlCommand.Parameters.Add(new SqlParameter("@ToDate", toDate));
                    sqlCommand.Parameters.Add(new SqlParameter("@ContractId", contractId));
                    sqlCommand.Parameters.Add(new SqlParameter("@Status", statusParameter));
                    sqlCommand.Parameters.Add(new SqlParameter("@ContractorId", contractorId));
                    _sqlDataReader = sqlCommand.ExecuteReader();
                    if (_sqlDataReader.HasRows)
                    {
                        int id = 0;
                        while (_sqlDataReader.Read())
                        {
                            var timesheetId = new Guid(_sqlDataReader["TimesheetId"].ToString());
                            DateTime createDate = Convert.ToDateTime(_sqlDataReader["WorkDate"], CultureInfo.CurrentCulture);
                            float workingTime = float.Parse(_sqlDataReader["WorkingTime"].ToString(), CultureInfo.CurrentCulture);
                            string description = _sqlDataReader["Description"].ToString();
                            string status = _sqlDataReader["Name"].ToString();
                            if (id == 14) id = 0;

                            dataTable.Rows.Add(timesheetId, createDate, workingTime, description, status, id);
                            id++;
                        }
                    }

                    _sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="timesheetId"></param>
        /// <returns></returns>
        public DataTable FetchReasonByTimesheetId(Guid timesheetId)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("Reason", typeof(string));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }

            try
            {
                using (var sqlCommand = new SqlCommand("sp_Timesheet_SelectRejectMessageByTimesheetId", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@TimesheetId", timesheetId));
                    _sqlDataReader = sqlCommand.ExecuteReader();
                    if (_sqlDataReader.HasRows)
                    {
                        while (_sqlDataReader.Read())
                        {
                            string reason = _sqlDataReader["Reason"].ToString();

                            dataTable.Rows.Add(reason);
                        }
                    }

                    _sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }
        #endregion
    }

    /// <summary>
    /// Type of Batch affected row: Update, Insert,Delete,Inactive,Reject,Approved
    /// </summary>
    public enum BatchType
    {
        Update,
        Insert,
        Delete,
        Inactive,
        Reject,
        Approved
    }
}
